import sys
import os

sys.path.append(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))))

from config import result_bucket
from aws_model.athena_submit import submit
from sql_lambda.delete import delete_data
from base_class.inspect_logging import print_to_log
import datetime
import json
from aws_model.aws_stepfunc import send_task_success, send_task_failure


def result_sbbljl(event):
    date = event.get('p_date')
    if not date:
        date = datetime.datetime.today().strftime('%Y-%m-%d')
    table_name = sys._getframe().f_code.co_name
    delete_data(result_bucket,table_name, date)
    sql = f'''insert into fridge_biz_result.result_sbbljl
                (
                select
                    t6.*
                from
                (
                select
                    sn,
                    max(material) as material,
                    upper(max(model)) as model,
                    format_datetime(factory_date,'YYYY-MM-dd') as factory_date,
                    cast(max_by(if(iy=0,null,iyo),iy) as varchar) as iyo,
                    cast(max_by(if(fy=0,null,fyo),fy) as varchar)  as fyo,
                    cast(max_by(if(sy=0,null,syo),sy) as varchar) as syo,
                    cast(max_by(if(ty=0,null,tyo),ty) as varchar) as tyo,
                    format_datetime(date('{date}') - interval '1' month, 'YYYY-MM') as time
                from
                     (
                    select
                        *,
                        first_value(objectid) over(partition by sn,iy order by p_date,objectid) as iyo,
                        first_value(objectid) over(partition by sn,fy order by p_date,objectid) as fyo,
                        first_value(objectid) over(partition by sn,sy order by p_date,objectid) as syo,
                        first_value(objectid) over(partition by sn,ty order by p_date,objectid) as tyo
                    from
                        (
                        select
                            sn,
                            factory_date,
                            p_date,
                            objectid,
                            material,
                            model,
                            if(cast(year(factory_date) as varchar) = substr(p_date,1,4),1,0) iy,
                            if(date(p_date) < (factory_date + interval '1' year),1,0) fy,
                            if(date(p_date) < (factory_date + interval '2' year) and date(p_date) >= (factory_date + interval '1' year),1,0) sy,
                            if(date(p_date) < (factory_date + interval '3' year) and date(p_date) >= (factory_date + interval '2' year),1,0) ty
                        from
                             (
                            select
                                if(zzfld00000b is null,'',zzfld00000b) as sn,
                                substr(t1.zzfld00000b,4,7) as material,
                                if(m.model is null,regexp_extract(t1.zzfld00000b,'[a-zA-Z0-9]+-[a-zA-Z0-9]+'),m.model) as model,
                                COALESCE(
                                        TRY(
                                            date(
                                                concat(
                                                    t2.name,'-',
                                                    substr(t1.zzfld00000b,17,2),'-',
                                                    substr(t1.zzfld00000b,19,2)
                                                        )
                                                )
                                            ),date('1970-01-01')
                                       ) factory_date,
                                p_date as p_date,
                                objectid
                            from
                                ods.kyd_ods t1
                            left join
                                dim.material m
                            on
                                substr(t1.zzfld00000b,4,7) = m.material
                            left join
                                dim.tree_book t2
                            on
                                upper(substr(t1.zzfld00000b,16,1)) = t2.code and t2.classify_name = 'sn出厂年份'
                            where
                                length(t1.zzfld00000b) = 24
                            and
                                t1.zzfld000001 in ('QT30','QT20')
                            and
                                t1.zzfld00002ft != '安装'
                            and
                                regexp_extract(zzfld00002gt,'指导使用') is null
                            and
                                p_date < '{date}'
                            ) t3
                        )t4
                    )t5
                group by
                    sn,factory_date
                ) t6
                left join
                (
                     select
                         sn,
                         in_year_objectid iyo,
                         first_year_objectid fyo,
                         seconde_year_objectid syo,
                         third_year_objectid tyo
                     from(
                        select
                            *,row_number() over(partition by sn order by time desc) as rn
                        from
                             fridge_biz_result.result_sbbljl
                        where
                            time < format_datetime(date('{date}') - interval '1' month, 'YYYY-MM')
                       ) t
                     where
                        rn = 1
                ) t7
                on
                    t6.sn = t7.sn
                where
                    t7.sn is null
                or
                    (
                        t7.sn is not null
                        and
                      (
                        if(t6.iyo is null,'',t6.iyo) != if(t7.iyo is null,'',t7.iyo)
                        or
                        if(t6.fyo is null,'',t6.fyo) != if(t7.fyo is null,'',t7.fyo)
                        or
                        if(t6.syo is null,'',t6.syo) != if(t7.syo is null,'',t7.syo)
                        or
                        if(t6.tyo is null,'',t6.tyo) != if(t7.tyo is null,'',t7.tyo)
                        )
                    )
                )'''

    athena_response = submit(sql)
    print_to_log("athena_response: ", athena_response)
    output = {"msg": f"{table_name} submit ok", "Payload": {}}
    output = json.dumps(output)
    step_response = send_task_success(tasktoken, output)
    print_to_log("step_response: ", step_response)


if __name__ == '__main__':
    try:
        event = json.loads(sys.argv[1])
        tasktoken = event.get('TaskToken')
        result_sbbljl(event)
    except Exception as e:
        print_to_log("athena_submit 失败")
        print_to_log("原因： ", str(e))
        send_task_failure(str(e), f'执行{sys._getframe().f_code.co_filename}错误', tasktoken)

